package mydessertstore.persistence.impl;

import mydessertstore.domain.Category;
import mydessertstore.domain.Order;
import mydessertstore.persistence.DBUtil;
import mydessertstore.persistence.OrderDao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class OrderDaoImpl implements OrderDao {

    private static final String GET_ORDERS_BY_USERNAME = "SELECT " +
            "ORDERS.ORDERID, USERID AS username, ORDERDATE, " +
            "SHIPADDR1, SHIPADDR2, SHIPCITY, SHIPSTATE, SHIPZIP, SHIPCOUNTRY, " +
            "BILLADDR1, BILLADDR2, BILLCITY, BILLSTATE, BILLZIP, BILLCOUNTRY, " +
            "BILLTOLASTNAME, COURIER, TOTALPRICE, BILLTOFIRSTNAME, SHIPTOFIRSTNAME, " +
            "SHIPTOLASTNAME, CREDITCARD, EXPRDATE AS expiryDate, CARDTYPE, LOCALE, STATUS " +
            "FROM ORDERS, ORDERSTATUS WHERE ORDERS.USERID = ? " +
            "AND ORDERS.ORDERID = ORDERSTATUS.ORDERID " +
            "ORDER BY ORDERDATE";

    private static final String GET_ORDER = "SELECT " +
            "ORDERS.ORDERID, USERID AS username, ORDERDATE, " +
            "SHIPADDR1, SHIPADDR2, SHIPCITY, SHIPSTATE, SHIPZIP, SHIPCOUNTRY, " +
            "BILLADDR1, BILLADDR2, BILLCITY, BILLSTATE, BILLZIP, BILLCOUNTRY, " +
            "BILLTOLASTNAME, COURIER, TOTALPRICE, BILLTOFIRSTNAME, SHIPTOFIRSTNAME, " +
            "SHIPTOLASTNAME, CREDITCARD, EXPRDATE AS expiryDate, CARDTYPE, LOCALE, STATUS " +
            "FROM ORDERS, ORDERSTATUS WHERE ORDERS.ORDERID = ? " +
            "AND ORDERS.ORDERID = ORDERSTATUS.ORDERID ";

    private static final String INSERT_ORDER = "INSERT INTO ORDERS " +
            "(ORDERID, USERID, ORDERDATE, SHIPADDR1, SHIPADDR2, SHIPCITY, SHIPSTATE," +
            " SHIPZIP, SHIPCOUNTRY, BILLADDR1, BILLADDR2, BILLCITY, BILLSTATE," +
            " BILLZIP, BILLCOUNTRY, COURIER, TOTALPRICE, BILLTOFIRSTNAME, BILLTOLASTNAME, " +
            " SHIPTOFIRSTNAME, SHIPTOLASTNAME,  CREDITCARD, EXPRDATE, CARDTYPE, LOCALE)" +
            "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    private static final String INSERT_ORDER_STATUS = "INSERT INTO ORDERSTATUS " +
            "(ORDERID, LINENUM, TIMESTAMP, STATUS) VALUES (?, ?, ?, ?)";

    @Override
    public List<Order> getOrdersByUsername(String username) {
        List<Order> orderList = new ArrayList<Order>();

        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_ORDERS_BY_USERNAME);
            preparedStatement.setString(1, username);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Order order = new Order();

                order.setOrderId(resultSet.getInt("ORDERS.ORDERID"));
                order.setUsername(resultSet.getString("username"));
                order.setOrderDate(resultSet.getTimestamp("ORDERDATE"));
                order.setShipAddress1(resultSet.getString("SHIPADDR1"));
                order.setShipAddress2(resultSet.getString("SHIPADDR2"));
                order.setShipCity(resultSet.getString("SHIPCITY"));
                order.setShipState(resultSet.getString("SHIPSTATE"));
                order.setShipZip(resultSet.getString("SHIPZIP"));
                order.setShipCountry(resultSet.getString("SHIPCOUNTRY"));

                order.setBillAddress1(resultSet.getString("BILLADDR1"));
                order.setBillAddress2(resultSet.getString("BILLADDR2"));
                order.setBillCity(resultSet.getString("BILLCITY"));
                order.setBillState(resultSet.getString("BILLSTATE"));
                order.setBillZip(resultSet.getString("BILLZIP"));
                order.setBillCountry(resultSet.getString("BILLCOUNTRY"));
                order.setCourier(resultSet.getString("COURIER"));
                order.setTotalPrice(resultSet.getBigDecimal("TOTALPRICE"));
                order.setBillToFirstName(resultSet.getString("BILLTOFIRSTNAME"));
                order.setBillToLastName(resultSet.getString("BILLTOLASTNAME"));

                order.setShipToFirstName(resultSet.getString("SHIPTOFIRSTNAME"));
                order.setShipToLastName(resultSet.getString("SHIPTOLASTNAME"));
                order.setCreditCard(resultSet.getString("CREDITCARD"));
                order.setExpiryDate(resultSet.getString("expiryDate"));
                order.setCardType(resultSet.getString("CARDTYPE"));
                order.setLocale(resultSet.getString("LOCALE"));

                order.setStatus(resultSet.getString("STATUS"));

                orderList.add(order);
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closeStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
        return orderList;
    }

    @Override
    public Order getOrder(int orderId) {

        Order order = new Order();

        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_ORDER);
            preparedStatement.setInt(1, orderId);
            ResultSet resultSet = preparedStatement.executeQuery();

            if(resultSet.next()){

                order.setOrderId(resultSet.getInt("ORDERS.ORDERID"));
                order.setUsername(resultSet.getString("username"));
                order.setOrderDate(resultSet.getTimestamp("ORDERDATE"));
                order.setShipAddress1(resultSet.getString("SHIPADDR1"));
                order.setShipAddress2(resultSet.getString("SHIPADDR2"));
                order.setShipCity(resultSet.getString("SHIPCITY"));
                order.setShipState(resultSet.getString("SHIPSTATE"));
                order.setShipZip(resultSet.getString("SHIPZIP"));
                order.setShipCountry(resultSet.getString("SHIPCOUNTRY"));

                order.setBillAddress1(resultSet.getString("BILLADDR1"));
                order.setBillAddress2(resultSet.getString("BILLADDR2"));
                order.setBillCity(resultSet.getString("BILLCITY"));
                order.setBillState(resultSet.getString("BILLSTATE"));
                order.setBillZip(resultSet.getString("BILLZIP"));
                order.setBillCountry(resultSet.getString("BILLCOUNTRY"));
                order.setCourier(resultSet.getString("COURIER"));
                order.setTotalPrice(resultSet.getBigDecimal("TOTALPRICE"));
                order.setBillToFirstName(resultSet.getString("BILLTOFIRSTNAME"));
                order.setBillToLastName(resultSet.getString("BILLTOLASTNAME"));

                order.setShipToFirstName(resultSet.getString("SHIPTOFIRSTNAME"));
                order.setShipToLastName(resultSet.getString("SHIPTOLASTNAME"));
                order.setCreditCard(resultSet.getString("CREDITCARD"));
                order.setExpiryDate(resultSet.getString("expiryDate"));
                order.setCardType(resultSet.getString("CARDTYPE"));
                order.setLocale(resultSet.getString("LOCALE"));

                order.setStatus(resultSet.getString("STATUS"));
            }

            DBUtil.closeResultSet(resultSet);
            DBUtil.closeStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
        return order;
    }

    @Override
    public void insertOrder(Order order) {
        System.out.println("test 6");

        try {

            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_ORDER);

            preparedStatement.setInt(1, order.getOrderId());
            preparedStatement.setString(2, order.getUsername());
            preparedStatement.setTimestamp(3, (Timestamp)order.getOrderDate());

            preparedStatement.setString(4, order.getShipAddress1());
            preparedStatement.setString(5, order.getShipAddress2());
            preparedStatement.setString(6, order.getShipCity());
            preparedStatement.setString(7, order.getShipState());
            preparedStatement.setString(8, order.getShipZip());
            preparedStatement.setString(9, order.getShipCountry());

            preparedStatement.setString(10, order.getBillAddress1());
            preparedStatement.setString(11, order.getBillAddress2());
            preparedStatement.setString(12, order.getBillCity());
            preparedStatement.setString(13, order.getBillState());
            preparedStatement.setString(14, order.getBillZip());
            preparedStatement.setString(15, order.getBillCountry());
            preparedStatement.setString(16, order.getCourier());
            preparedStatement.setBigDecimal(17, order.getTotalPrice());
            preparedStatement.setString(18, order.getBillToFirstName());
            preparedStatement.setString(19, order.getBillToLastName());


            preparedStatement.setString(20, order.getShipToFirstName());
            preparedStatement.setString(21, order.getShipToLastName());
            preparedStatement.setString(22, order.getCreditCard());
            preparedStatement.setString(23, order.getExpiryDate());
            preparedStatement.setString(24, order.getCardType());
            preparedStatement.setString(25,order.getLocale());

            preparedStatement.executeUpdate();

            System.out.println("successfully insert");
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);

        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void insertOrderStatus(Order order) {
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_ORDER_STATUS);
            preparedStatement.setInt(1, order.getOrderId());
            preparedStatement.setInt(2, order.getOrderId());
            preparedStatement.setTimestamp(3, (Timestamp) order.getOrderDate());
            preparedStatement.setString(4, order.getStatus());

            preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}
